﻿CREATE PROCEDURE [dbo].[proc_Customer_CallOnTongJi]
(
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),
	@CompanyId int,
	@DepartmentId int
)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@departmentid

	;WITH List1 As (
		Select departmentid,genJinId,Count(0) As Shl From Customer_Track
		Where CompanyId=@CompanyId And uptime>=@sRq And uptime<=@eRq
		And departmentid In(Select Id From Department Where CompanyId=@CompanyId And Left(bmbh,Len(@bmbh_T))=@bmbh_T)
		Group By departmentid,genJinId
	),
	List2 As (
		Select departmentid,resultFlag,Count(0) As Shl From CheckInOut
		Where CompanyId=@CompanyId And CheckOutTime>=@sRq And CheckOutTime<=@eRq
		And departmentid In(Select Id From Department Where CompanyId=@CompanyId And Left(bmbh,Len(@bmbh_T))=@bmbh_T)
		Group By departmentid,resultFlag
	)
	Select d.Id,d.ParentId,
	(Case When Len(bmbh)>4 Then Replicate('┆┄┄',(Len(bmbh)-4)/2)+IsNull(dname,'') Else '◆'+IsNull(dname,'') End) As DName,
	IsNull((Select Sum(shl) From List1 Where departmentid=d.id And genJinId In(1,6)),0) As YiHouContact,
	IsNull((Select shl From List1 Where departmentid=d.id And genJinId=2),0) As ZanBuContact,
	IsNull((Select shl From List1 Where departmentid=d.id And genJinId=3),0) As GiveUp,
	IsNull((Select shl From List1 Where departmentid=d.id And genJinId=4),0) As TelNullErrorNo,
	IsNull((Select shl From List1 Where departmentid=d.id And genJinId=5),0) As OldCustomer,
	0 as CustomerType,
	0 As ToolComment,
	IsNull((Select shl From List2 Where departmentid=d.id and resultFlag=1),0) as Effective,
	IsNull((Select shl From List2 Where departmentid=d.id and resultFlag=2),0) as Invalid,
	IsNull((Select shl From List2 Where departmentid=d.id and resultFlag=0),0) as NotSign --未签回
	From Department d
	Where CompanyId = @CompanyId And Left(bmbh,Len(@bmbh_T))=@bmbh_T
	Order By D.bmbh

--	Select d.Id,d.ParentId,
--	(case when len(bmbh)>4 then replicate('┆┄┄',(len(bmbh)-4)/2)+isnull(dname,'') else '◆'+isnull(dname,'') end) as DName,
--	(Select count(0) From Customer_Track Where departmentid=d.id and genJinId In(1,6) and uptime>=@sRq And uptime<=@eRq) As YiHouContact,
--	(Select count(0) From Customer_Track Where departmentid=d.id and genJinId=2 and uptime>=@sRq And uptime<=@eRq) As ZanBuContact,
--	(Select count(0) From Customer_Track Where departmentid=d.id and genJinId=3 and uptime>=@sRq And uptime<=@eRq) As GiveUp,
--	(Select count(0) From Customer_Track Where departmentid=d.id and genJinId=4 and uptime>=@sRq And uptime<=@eRq) As TelNullErrorNo,
--	(Select count(0) From Customer_Track Where departmentid=d.id and genJinId=5 and uptime>=@sRq And uptime<=@eRq) As OldCustomer,
--	(Select count(0) From Customer_Track Where departmentid=d.id And PublicityDemandId<>0 And NetworkConsciousnessId<>0 And uptime>=@sRq And uptime<=@eRq) as CustomerType,
--	(Select count(0) From Customer_Subjoin_List Where departmentid=d.id and uptime>=@sRq And uptime<=@eRq) As ToolComment,
--	(Select count(0) From CheckInOut Where departmentid=d.id and resultFlag=1 and CheckOutTime>=@sRq And CheckOutTime<=@eRq) as Effective,
--	(Select count(0) From CheckInOut Where departmentid=d.id and resultFlag=2 and CheckOutTime>=@sRq And CheckOutTime<=@eRq) as Invalid,
--	(Select count(0) From CheckInOut Where departmentid=d.id and resultFlag=0 and CheckOutTime>=@sRq And CheckOutTime<=@eRq AND CheckInOut.CheckInTime IS NULL ) as NotSign --未签回
--	From Department d
--	Where CompanyId = @companyid And Left(bmbh,Len(@bmbh_T))=@bmbh_T
--	Order By D.bmbh
End
